﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MvcApplication1.Models;

namespace MvcApplication1.Controllers
{
    public class DbQuery
    {
        private static readonly ProductEntities _productDb = new ProductEntities();

        public IEnumerable<Category> CategoryList
        {
            get { return DbQuery._productDb.Categories; }
        }

        public Category getCategoryByID(int? id, int? cpage, int cpp, int? ppage, int ppp)
        {
            id = id.HasValue ? id.Value : 0;
            cpage = cpage.HasValue ? cpage.Value - 1 : 0;
            ppage = ppage.HasValue ? ppage.Value - 1 : 0;
            var category = _productDb.Categories
                .SingleOrDefault(c => c.CategoryID == id);

            category.SubCategoryCount = _productDb.Categories.Where(c => c.ParentID == id).Count();
            category.SubCategoryList = _productDb.Categories.Where(c => c.ParentID == id)
                .Select(c => new SubCategory()
                {
                    CategoryID = c.CategoryID,
                    ParentID = c.ParentID,
                    CategoryName = c.CategoryName,
                    CategoryDescription = c.CategoryDescription
                })
                .OrderBy(sc => sc.CategoryName).Skip((int)cpage * cpp).Take(cpp);
            category.Products = _productDb.Products.Where(p => p.CategoryId == id)
                .OrderBy(p => p.Priority).Skip((int)ppage * ppp).Take(ppp)
                .ToList();
            category.ProductCount = category.Products.Count;

            return category;
        }

        public Category getCategoryByName(string name)
        {
            return _productDb.Categories.SingleOrDefault(c => c.CategoryName.Equals(name, StringComparison.CurrentCultureIgnoreCase));
        }

        public IEnumerable<Category> getSubCategoryList(int id, int? page)
        {
            int skip = page.HasValue ? page.Value - 1 : 0;
            return _productDb.Categories.Include("Products")
                    .Where(c => c.ParentID == id && c.CategoryID != 0)
                    .OrderBy(c => c.CategoryName).Skip(skip * 10).Take(10);
        }

        public int countSubCategory(int id)
        {
            return _productDb.Categories
                    .Where(c => c.ParentID == id && c.CategoryID != 0).Count();
        }

    }
}